package com.efesco.stat.finance;

import java.util.List;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.DBStatement;
import com.bstek.dorado.utils.variant.VariantSet;
import com.bstek.dorado.common.*;
import com.efesco.common.DBTools;
import com.efesco.common.Tools;
import com.efesco.system.User;

/**
 * monthReport_payDatasetListener
 */
public class monthReport_payDatasetListener extends AbstractDatasetListener {
  public void afterLoadData(Dataset dataset)
     throws Exception {
		DoradoContext context = DoradoContext.getContext();
	    String sql="",foreign_enterprise_id="",fesc_user_id="";

	    Object obj = context.getAttribute(context.SESSION, "User");
	    if (obj != null) {
	    	User user = (User) obj;
    		foreign_enterprise_id=user.getForeignEnterpriseId();
    		fesc_user_id=user.getForEmployeeId();
    		String startMonth="",endMonth="",singleCompanyId="";

    		startMonth = Tools.toStr(dataset.parameters().getString("startMonth"));
    		endMonth = Tools.toStr(dataset.parameters().getString("endMonth"));
    		singleCompanyId = Tools.toStr(dataset.parameters().getString("singleCompanyId"));
    		
	    	sql="select cmp.company_code,cmp.company_chinese_name,pa.pay_month,pa.payment_type,nvl(sum(pad.company_amount),0)+nvl(sum(pad.employee_amount),0) amount " +
	    			"from s_single_company cmp,s_pay_audit pa,s_pay_audit_detail pad,s_company_businessman cbm " +
	    			"where pa.pay_audit_id=pad.pay_audit_id and pad.single_company_id=cmp.single_company_id " +
	    			"and cmp.single_company_id=cbm.single_company_id and pa.status=4  " +
	    			"and cmp.foreign_enterprise_id="+foreign_enterprise_id+
    				" and exists " + DBTools.getRightSqlByBusinessman(fesc_user_id,"cbm") ;

    		if (!startMonth.equals(""))  {
    			sql = sql + " and pa.pay_month >= '"+startMonth+"' ";
    		}
    		if (!endMonth.equals(""))  {
    			sql = sql + " and pa.pay_month <= '"+endMonth+"' ";
    		}
    		if (!singleCompanyId.equals(""))  {
    			sql = sql + " and cmp.single_company_id = "+singleCompanyId;
    		}
	
    		sql = sql + " group by cmp.single_company_id,cmp.company_code,cmp.company_chinese_name,pa.pay_month,pa.payment_type  " ;
	
    		DBStatement statement = new DBStatement();
    		statement.setSql(sql);
    		List list=statement.queryForList();
    		
    		String tag="";

    		for (int j=0;j<list.size();j++)  {
    			VariantSet var=(VariantSet)list.get(j);
    			if (tag.equals(var.getString("company_code")+var.getString("pay_month"))) {
    				dataset.setString(var.getString("payment_type"),var.getString("amount"));
    			} 
    			else {
    				dataset.insertRecord();
    				tag = var.getString("company_code")+var.getString("pay_month");
    				dataset.setString("company_code", var.getString("company_code"));
    				dataset.setString("company_chinese_name", var.getString("company_chinese_name"));
    				dataset.setString("pay_month", var.getString("pay_month"));
    				dataset.setString(var.getString("payment_type"),var.getString("amount"));
    			}
    		}
    		Tools.toSumDataset(dataset,"company_chinese_name");
    		statement.close();
	    }
  }

}
